﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using KongMing.ORM.SqlSugar.Models;
using SqlSugar;

namespace KongMing.ORM.SqlSugar
{
    public class 分组查询
    {
        public static void GroupQuery() 
        {
            using (var db = SqlSugarHelper.GetDB())
            {
                //1、基础分组语句
                //通过作者分组，查每个作者书的进价平均值,筛选大于10的，降序排列
                //等于sql：select Author 作者,AVG(InPrice) 平均进价 from Book where IsDelete=0 group by Author having AVG(InPrice)>=10 order by 平均进价 desc
                var group_list = db.Queryable<Book>().GroupBy(x => new { x.Author })
                        .Where(x => x.IsDelete == 0)
                        .Having(x => SqlFunc.AggregateAvg(x.InPrice) >= 10)
                        .Select(x => new { 作者 = x.Author, 平均进价 = SqlFunc.AggregateAvg(x.InPrice) })
                        .OrderBy(x => x.平均进价, OrderByType.Desc).ToList();

                

                //3、SQL Server开窗函数分组，每组取N条（不是所有数据库都支持）
                //等于sql：select * from(select *,ROW_NUMBER() over(partition by author order by inprice desc) parId  from Book)n where parId<=3
                var query_pritition = db.Queryable<Book>().PartitionBy(x => x.Author).OrderBy(x => x.InPrice, OrderByType.Desc).Take(3).ToList();

                
            }
            
        }
    }
}
